package com.qingsong.mapper;

import com.qingsong.entity.Emp;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;
import java.util.List;

/**

 * @author qingsong

 */

@Repository

public interface EmpMapper {



    @Insert("insert into ums_emp " +
            "(`username`, `password`, `avatar`, `phone`, `wechat`, `email`, `gender`, " +
            "`age`, `province`, `address`, `realname`, `idcard`, `info`, " +
            "`fk_dept_id`, `hiredate`, `deleted`, `created`, `updated`) " +
            "values (#{username}, #{password}, #{avatar}, #{phone}, #{wechat}, #{email}, " +
            "#{gender}, #{age}, #{province}, #{address}, #{realname}, #{idcard}, #{info}," +
            "#{fkDeptId}, #{hiredate}, #{deleted}, #{created}, #{updated})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(Emp emp);



    @Select("select ue.id, ue.username, ue.password, ue.avatar, ue.phone, ue.wechat, ue.email, " +

            "ue.gender, ue.age, ue.province, ue.address, ue.realname, ue.idcard, " +

            "ue.fk_dept_id, ue.info, ue.hiredate, ue.deleted, ue.created, ue.updated from ums_emp ue " +

            "where ue.id = #{param1} and ue.deleted = 0")
    Emp selectById(Long id);



    @Update("<script>" +

            "update ums_emp " +

            "<set> " +

            "<if test='username != null'> username = #{username}, </if> " +

            "<if test='password != null'> password = #{password}, </if> " +

            "<if test='avatar != null'> avatar = #{avatar}, </if> " +

            "<if test='phone != null'> phone = #{phone}, </if> " +

            "<if test='wechat != null'> wechat = #{wechat}, </if> " +

            "<if test='email != null'> email = #{email}, </if> " +

            "<if test='gender != null'> gender = #{gender}, </if> " +

            "<if test='age != null'> age = #{age}, </if> " +

            "<if test='province != null'> province = #{province}, </if> " +

            "<if test='address != null'> address = #{address}, </if> " +

            "<if test='realname != null'> realname = #{realname}, </if> " +

            "<if test='idcard != null'> idcard = #{idcard}, </if> " +

            "<if test='fkDeptId != null'> fk_dept_id = #{fkDeptId}, </if> " +

            "<if test='info != null'> info = #{info}, </if> " +

            "<if test='hiredate != null'> hiredate = #{hiredate}, </if> " +

            "<if test='deleted != null'> deleted = #{deleted}, </if> " +

            "<if test='created != null'> created = #{created}, </if> " +

            "<if test='updated != null'> updated = #{updated}, </if> " +

            "</set> " +

            "where id = #{id} and deleted = 0" +

            "</script>")

    int updateById(Emp emp);



    @Update("update ums_emp set deleted = 1, updated = now() where id = #{id}")

    int logicDeleteById(Long id);



     @Update("<script>" +

            "update ums_emp set deleted = 1, updated = now() where " +

            "<foreach collection='list' item='e' open='id in (' close=')' separator=','>" +

            "${e}" +

            "</foreach>" +

            "</script>")

    int logicDeleteByIds(List<Long> ids);



    @Results(id = "resultMap", value = {
            @Result(column = "id", property = "id", id = true),
            @Result(column = "fk_dept_id", property = "fkDeptId"),
            @Result(column = "fk_dept_id", property = "dept",
                    one = @One(select = "com.qingsong.mapper.DeptMapper.selectById")),
    })
    @Select("select ue.id, ue.username, ue.password, ue.avatar, ue.phone, ue.wechat, ue.email, " +

            "ue.gender, ue.age, ue.province, ue.address, ue.realname, ue.idcard, " +

            "ue.fk_dept_id, ue.info, ue.hiredate, ue.deleted, ue.created, ue.updated from ums_emp ue " +

            "where ue.deleted = 0")

    List<Emp> list();



    @Select("select ue.id, ue.username, ue.password, ue.avatar, ue.phone, ue.wechat, ue.email, " +

            "ue.gender, ue.age, ue.province, ue.address, ue.realname, ue.idcard, " +

            "ue.fk_dept_id, ue.info, ue.hiredate, ue.deleted, ue.created, ue.updated from ums_emp ue " +

            "where ue.email = #{param1}")

    Emp selectByEmail(String email);



    @Select("select ue.id, ue.username, ue.password, ue.avatar, ue.phone, ue.wechat, ue.email, " +

            "ue.gender, ue.age, ue.province, ue.address, ue.realname, ue.idcard, " +

            "ue.fk_dept_id, ue.info, ue.hiredate, ue.deleted, ue.created, ue.updated from ums_emp ue " +

            "where ue.idcard = #{param1}")

    Emp selectByIdcard(String idcard);



    @Select("select ue.id, ue.username, ue.password, ue.avatar, ue.phone, ue.wechat, ue.email, " +

            "ue.gender, ue.age, ue.province, ue.address, ue.realname, ue.idcard, " +

            "ue.fk_dept_id, ue.info, ue.hiredate, ue.deleted, ue.created, ue.updated from ums_emp ue " +

            "where ue.phone = #{param1}")

    Emp selectByPhone(String phone);



    @Select("select ue.id, ue.username, ue.password, ue.avatar, ue.phone, ue.wechat, ue.email, " +

            "ue.gender, ue.age, ue.province, ue.address, ue.realname, ue.idcard, " +

            "ue.fk_dept_id, ue.info, ue.hiredate, ue.deleted, ue.created, ue.updated from ums_emp ue " +

            "where ue.username = #{param1}")

    Emp selectByUsername(String username);



    @Select("select ue.id, ue.username, ue.password, ue.avatar, ue.phone, ue.wechat, ue.email, " +

            "ue.gender, ue.age, ue.province, ue.address, ue.realname, ue.idcard, " +

            "ue.fk_dept_id, ue.info, ue.hiredate, ue.deleted, ue.created, ue.updated from ums_emp ue " +

            "where ue.wechat = #{param1}")

    Emp selectByWechat(String wechat);

    @Select("select us.id, us.username, us.password, us.avatar, us.phone, us.wechat, us.email, us.gender," +
            "us.age, us.province, us.address, us.realname, us.idcard, us.info, us.hiredate ," +
            "us.deleted, us.created, us.updated from ums_emp us " +
            "where us.username = #{username} " +
            "and us.phone = #{phone} " +
            "and us.wechat = #{wechat} " +
            "and us.email = #{email} " +
            "and us.idcard = #{idcard} ")
    Emp selectByAllTitle(Emp emp);
    
    @Delete("delete from ums_emp where id = #{param1}")

    int deleteById(Long id);

    @ResultMap("resultMap")
    @Select("select us.id, us.username, us.password, us.avatar, us.phone, us.wechat, us.email, us.gender," +
            "us.age, us.province, us.address, us.realname, us.idcard, us.info, us.hiredate ," +
            "us.deleted, us.created, us.updated from ums_emp us " +
            "where us.username = #{param1} " +
            "and us.password = #{param2} " +
            "and us.deleted = 0 ")
    Emp selectByAccount(String username, String password);

    @ResultMap("resultMap")
    @Select("select ue.id, ue.username, ue.password, ue.avatar, ue.phone, ue.wechat, ue.email, " +

            "ue.gender, ue.age, ue.province, ue.address, ue.realname, ue.idcard, " +

            "ue.fk_dept_id, ue.info, ue.hiredate, ue.deleted, ue.created, ue.updated from ums_emp ue " +

            "where ue.deleted = 0 and ue.realname like concat('%', #{param1}, '%')")
    List<Emp> listLikeTitle(String realname);

    @Update("update ums_emp set password = #{param1}, updated = now() " +
            "where id = #{param3} and password = #{param2} and deleted = 0")
    int updatePassword(String newPassword, String oldPassword, Long id);

    @Select("select count(*) > 0 from ums_emp us " +
            "where us.fk_dept_id = #{param1} and us.deleted = 0")
    boolean existsDeptId(Long fkDeptId);

    @Select("<script>" +
            "select count(*) > 0 from ums_emp us " +
            "where " +
            "<foreach item= 'e' collection = 'list' open = 'us.fk_dept_id in (' close=')' separator=','> " +
            "#{e}" +
            "</foreach>" +
            "and us.deleted = 0 " +
            "</script>")
    boolean existsDeptIds(List<Long> fkDeptIds);
}